import os
import sys
import win32com.client

import numpy as np
import pandas as pd

from DrissionPage import ChromiumOptions, Chromium

密码库 = {
    '广州': ['admin', 'admin'],
    '佛山三山': ['admin', '888888'],
    '深圳': ['csc_other', 'zxm10***'],
    '松山湖': ['fszc', 'zxm10***'],
    '科学城': ['ganyong', 'Admin@123']
}

# Dataframe设置不显示小数点
pd.set_option('display.precision', 0)


def desktop():
    return os.path.join(os.path.expanduser("~"), 'Desktop')


def Web_page_retrieval():
    '''网页获取'''
    Web_page_data = dict()
    import ddddocr
    global 密码库
    co = ChromiumOptions()  #.headless()
    Chr = Chromium(co)
    tab = Chr.latest_tab

    '''广州'''
    try:
        tab.get('http://212.11.36.214/USMS/Default.aspx')
        if tab.url == 'http://212.11.36.214/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('admin')
            tab.ele('@name=ctl00$Main$tbPass').input('admin\n')
        tab.wait(2)
        Guangzhou = tab('@id=ctl00_lbAlarm').texts()
        Guangzhou = [
            int(Guangzhou[0]),
            int(Guangzhou[1]),
            int(Guangzhou[2]),
            int(Guangzhou[3])
        ]
        Web_page_data['广州'] = Guangzhou
        print('广州',Guangzhou)
    except:
        print('广州获取失败')

    '''深圳'''
    try:
        tab.get('http://212.11.82.91/USMS/login.aspx')
        if tab.url == 'http://212.11.82.91/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('admin')
            tab.ele('@name=ctl00$Main$tbPass').input('admin@2020\n')
        tab.wait(2)
        Shenzhen = tab('@id=ctl00_lbAlarm').texts()
        Shenzhen = [
            int(Shenzhen[0]) ,
            int(Shenzhen[1]) ,
            int(Shenzhen[2]) ,
            int(Shenzhen[3]) 
        ]
        Web_page_data['深圳'] = Shenzhen
        print('深圳',Shenzhen)
    except:
        print('深圳获取失败')

    '''湛江'''
    try:
        tab.get('http://212.11.232.77/USMS/Default.aspx')
        if tab.url == 'http://212.11.232.77/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('admin')
            tab.ele('@name=ctl00$Main$tbPass').input('admin\n')
        tab.wait(2)
        Zhanjiang = tab('@id=ctl00_lbAlarm').texts()
        Zhanjiang = [int(Zhanjiang[0]), int(Zhanjiang[1]), int(Zhanjiang[2]), int(Zhanjiang[3])]
        Web_page_data['湛江'] = Zhanjiang
        print('湛江',Zhanjiang)
    except:
        print('湛江获取失败')

    '''清远'''
    try:
        tab.get('http://212.12.0.154/USMS/Default.aspx')
        if tab.url == 'http://212.12.0.154/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('admin')
            tab.ele('@name=ctl00$Main$tbPass').input('admin\n')
        tab.wait(2)
        Qingyuan = tab('@id=ctl00_lbAlarm').texts()
        Qingyuan = [
            int(Qingyuan[0]) - 8,
            int(Qingyuan[1]) - 6,
            int(Qingyuan[2]) - 1,
            int(Qingyuan[3]) - 3
        ]
        Web_page_data['清远'] = Qingyuan
        print('清远',Qingyuan)
    except:
        print('清远获取失败')

    '''茂名'''
    try:
        tab.get('http://212.11.246.77/USMS/Default.aspx')
        if tab.url == 'http://212.11.246.77/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('fszc')
            tab.ele('@name=ctl00$Main$tbPass').input('admin\n')
        tab.wait(2)
        Maoming = tab('@id=ctl00_lbAlarm').texts()
        Maoming = [
            int(Maoming[0]) - 9,
            int(Maoming[1]) - 2,
            int(Maoming[2]),
            int(Maoming[3]) - 1
        ]
        Web_page_data['茂名'] = Maoming
        print('茂名',Maoming)
    except:
        print('茂名获取失败')

    '''河源'''
    try:
        tab.get('http://212.12.35.76/USMS/Default.aspx')
        if tab.url == 'http://212.12.35.76/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('admin')
            tab.ele('@name=ctl00$Main$tbPass').input('admin\n')
        tab.wait(2)
        Headwaters = tab('@id=ctl00_lbAlarm').texts()
        Headwaters = [
            int(Headwaters[0]),
            int(Headwaters[1]),
            int(Headwaters[2]) - 6,
            int(Headwaters[3])
        ]
        Web_page_data['河源'] = Headwaters
        print('河源',Headwaters)
    except:
        print('河源获取失败')

    '''汕尾'''
    try:
        tab.get('http://212.12.38.77/USMS/Default.aspx')
        if tab.url == 'http://212.12.38.77/USMS/login.aspx':
            tab.ele('@name=ctl00$Main$tbUser').input('admin')
            tab.ele('@name=ctl00$Main$tbPass').input('admin\n')
        tab.wait(2)
        Shanwei = tab('@id=ctl00_lbAlarm').texts()
        Shanwei = [
            int(Shanwei[0]),
            int(Shanwei[1]),
            int(Shanwei[2]),
            int(Shanwei[3])
        ]
        Web_page_data['汕尾'] = Shanwei
        print('汕尾',Shanwei)
    except:
        print('汕尾获取失败')

    '''科学城'''
    # try:
        # tab.get('http://212.11.9.1:8090/peim/views/login')
        # if tab.url == 'http://212.11.9.1:8090/peim/views/login':
        #     userName = tab.ele('@id=tbUser')
        #     userName.input(密码库['科学城'][0])
        #     password = tab.ele('@id=tbPass')
        #     password.input(密码库['科学城'][1])
        #     img = tab.ele('x:/html/body/form/div[3]/div/div/ul/ol/img'
        #                   ).get_screenshot(as_bytes='png')
        #     ocr = ddddocr.DdddOcr(beta=True, show_ad=False)  # 切换为第二套ocr模型
        #     result = ocr.classification(img)
        #     Captcha = tab.ele('@id=identifyCode')
        #     Captcha.input(result + '\n')
        # Science_City = tab.ele('t:li@@class=alarmboxes').text.split('\n')
        # Web_page_data['科学城'] = Science_City
        # print('科学城',Science_City)
    # except:
    #     print('科学城获取失败')
    '''佛山三山'''
    try:
        tab.get('http://212.11.150.50/peim/views/login')
        userName = tab.ele('@id=tbUser')
        userName.input(密码库['佛山三山'][0])
        password = tab.ele('@id=tbPass')
        password.input(密码库['佛山三山'][1])
        img = tab.ele('x:/html/body/form/div[3]/div/div/ul/ol/img').get_screenshot(
            as_bytes='png')
        ocr = ddddocr.DdddOcr(beta=True, show_ad=False)  # 切换为第二套ocr模型
        result = ocr.classification(img)
        Captcha = tab.ele('@id=identifyCode')
        Captcha.input(result + '\n')
        tab.wait.load_start()
        tab.get('http://212.11.150.50/vuepeim/alarmView')
        Foshan_Sanshan = tab.ele('t:div@@class=alarm-amount').eles('t:span')
        Foshan_Sanshan = [i.text for i in Foshan_Sanshan]
        Web_page_data['佛山三山'] = Foshan_Sanshan
    except:
        print('佛山三山获取失败')
    Chr.quit()
    return Web_page_data


def znv(new_path):
    '''力维'''
    # 读取文件
    df = pd.read_excel(new_path, header=5)

    # 判断是否存在 'CSC监控中心' or 'LSC监控中心'，使用布尔索引删除指定地点的行
    if ('CSC监控中心' or 'LSC监控中心') in df['局站类型'].values:
        df = df[~df['局站类型'].isin(['CSC监控中心', 'LSC监控中心'])]

    # 监控中心归类地市
    df['监控中心'] = df['监控中心'].str[:2]

    # 统计告警等级数据
    result = df.groupby(['监控中心', '告警级别']).size().unstack(fill_value=0)

    # 更新列名
    result.rename(columns={
        '一级': '一级告警',
        '二级': '二级告警',
        '三级': '三级告警',
        '四级': '四级告警'
    },
                  inplace=True)
    result = result.reindex(columns=levels_order)
    佛山 = result.loc['佛山'].to_list()
    佛山 = [佛山[0] - 3, 佛山[1] - 2, 佛山[2] - 13, 佛山[3]]
    result.loc['佛山'] = 佛山

    try:
        #获取网页数据
        print('正在获取网页数据...')
        Web_page_data = Web_page_retrieval(
        )
        print('网页数据获取成功！')
        for j,i in Web_page_data.items():
            result.loc[j] = i
        result = result.astype('int')

        for i in ['惠州', '揭阳', '韶关', '白云北', '知识城', '梅州', '潮州', '松山湖']:
            result.loc[i] = ['/', '/', '/', '/']
        # 重新排序，按指定顺序排序行列
        output = result.reindex(index=locations_order, columns=levels_order)
    except Exception as e:
        print(e)
        print('无法获取网页数据！请自行获取数据！')
        result = result.astype('int')

        for i in ['惠州', '揭阳', '韶关', '白云北', '知识城', '梅州', '潮州']:
            result.loc[i] = ['/', '/', '/', '/']
        # 重新排序，按指定顺序排序行列
        output = result.reindex(index=locations_order, columns=levels_order)
        # 将NAN空值设为0
        output.fillna(0, inplace=True)

        # []中数据留空
        tmp = ['广州', '湛江', '清远', '茂名', '河源', '汕尾']
        mask = ~output.index.isin(tmp)  # ~ 表示取反，即选择不在[]中的行

        # 使用掩码和fillna函数填充空值
        output.loc[mask, :] = output.loc[mask, :].fillna(0)
        output.loc[tmp, :] = np.nan

    return output


def vertiv(new_path):
    '''维谛'''
    # 读取文件
    df = pd.read_excel(new_path)

    # 判断是否存在 '佛山联通三山机楼' or '广东'，使用布尔索引删除指定地点的行
    if ('佛山联通三山机楼' or '广东') in df['中心名称'].values:
        df = df[~df['中心名称'].isin(['佛山联通三山机楼', '广东'])]

    # 定义地点映射字典
    location_map = {
        '广州联通': '广州',
        '江门联通维谛动环监控系统': '江门',
        '佛山联通': '佛山',
        '肇庆联通': '肇庆',
        '中山联通动环监控': '中山',
        '湛江联通': '湛江',
        '汕头联通': '汕头',
        '中国联通华南（东莞）数据中心二期': '松山湖',
        '惠州联通动环监控系统': '惠州',
        '珠海联通监控中心': '珠海',
        '韶关联通': '韶关',
        '广州白云联通IDC': '白云北',
        '东莞联通监控中心': '东莞',
        '揭阳联通动环监控': '揭阳',
        '梅州联通动环监控': '梅州',
        '潮州联通监控中心': '潮州',
        '广州联通知识城': '知识城',
        '阳江联通IDC机房': '阳江',
        '汕尾联通': '汕尾',
        '清远联通': '清远'
    }

    # 使用map函数进行地点名称的映射
    df['中心名称'] = df['中心名称'].map(location_map)

    # 使用groupby进行统计
    result = df.groupby(['中心名称', '告警等级']).size().unstack(fill_value=0)

    # 创建一个新的DataFrame或Series，包含要插入的行数据
    new_rows = pd.DataFrame(
        {
            '一级告警': ['/', '/', '/', '/', '/', '/'],
            '二级告警': ['/', '/', '/', '/', '/', '/'],
            '三级告警': ['/', '/', '/', '/', '/', '/'],
            '四级告警': ['/', '/', '/', '/', '/', '/']
        },
        index=['深圳', '茂名', '河源', '云浮', '科学城', '佛山三山'])  # 设置新行的索引

    # 使用append方法插入多行
    output = result._append(new_rows, ignore_index=None)

    # 根据定义的顺序重排DataFrame的行和列
    output = output.reindex(index=locations_order, columns=levels_order)

    # 将NAN空值设为0
    output.fillna(0, inplace=True)

    return output


desktop_path = desktop()
folder = desktop_path + "\\每日动环"
names = os.listdir(folder)

# 定义地点和等级的期望顺序
locations_order = [
    '广州', '深圳', '东莞', '佛山', '中山', '惠州', '珠海', '江门', '汕头', '湛江', '揭阳', '肇庆',
    '韶关', '清远', '茂名', '梅州', '潮州', '阳江', '河源', '汕尾', '云浮', '科学城', '松山湖', '白云北',
    '知识城', '佛山三山'
]
levels_order = ['一级告警', '二级告警', '三级告警', '四级告警']

for name in names:
    path = os.path.join(folder, name)
    print(path)
    if "notcleardevicealarmdetail" in name or "广东省" in name:
        res_zvn = znv(path)
        # print(path)
    elif '2025' in name[:6]:
        res_vert = vertiv(path)
        # print(path)

if 'res_zvn' in dir() and 'res_vert' not in dir():
    res_vert = res_zvn.copy()
    res_vert[:] = np.nan
elif 'res_zvn' not in dir() and 'res_vert' in dir():
    res = res_vert.copy()
    res_vert[:] = np.nan
elif 'res_zvn' not in dir() and 'res_vert' not in dir():
    print('不存在告警！')
    sys.exit()

res = pd.concat([res_zvn, res_vert], axis=1)

# 添加新列
res['IP地址'] = [
    '212.11.36.214', '', '', '', '', '', '', '', '', '212.11.232.77', '', '',
    '', '212.12.0.154', '212.11.246.77', '212.12.27.76', '', '',
    '212.12.35.76', '212.12.38.77:33899', '', '', '', '', '', ''
]

res['链接'] = [
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\广州.bat' + '","链接")',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\深圳.bat' + '","链接")', '', '', '',
    '', '', '', '',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\湛江.bat' + '","链接")', '', '', '',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\清远.bat' + '","链接")',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\茂名.bat' + '","链接")',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\梅州.bat' + '","链接")', '', '',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\河源.bat' + '","链接")',
    '=HYPERLINK("' + r'D:\lz\gitee\test\bat\汕尾.bat' + '","链接")', '', '', '',
    '', '', ''
]
# print(res)
#维谛东莞一级减8
res.iloc[2, 4] = res.iloc[2, 4] - 8
#维谛白云北一级减13
res.iloc[23, 4] = res.iloc[23, 4] - 13

try:
    # 获取 Excel 应用程序对象
    excel = win32com.client.GetActiveObject('Excel.Application')
    # 遍历所有打开的工作簿
    for workbook in excel.Workbooks:
        if workbook.Name == '每日动环告警数量统计.xlsx':
            # 关闭指定的工作簿
            workbook.Close(SaveChanges=False)  # 如果需要保存更改，改为 SaveChanges=True
            print('成功关闭工作簿: 每日动环告警数量统计.xlsx')
            break
except:
    pass
res.to_excel(desktop_path + "\\每日动环告警数量统计.xlsx",
             sheet_name="sheet1",
             index=True,
             header=True)
print('转换成功！！！')
xlsx = os.system(f"start {desktop()}\每日动环告警数量统计.xlsx")
